﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using YT.DB;
using YT.Commons;
using YT.AdvanceCommons;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;

namespace YT.XH.MF
{
    public partial class frmExportToExcel : Form
    {
        private string mstrType = string.Empty;
        DBAccess dbcon = new DBAccess(false, false);
        public frmExportToExcel()
        {
            InitializeComponent();
        }

        private void btnExit_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btnExcel_Click(object sender, EventArgs e)
        {
            string strSQL=txtSQL.Text ;
            if (strSQL.Trim () == "")
            {
                MessageBox.Show("导出的语句不能为空!", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            if (VaildCheck( strSQL  ))
            {
                ToExcel(strSQL  );      
            }
        }        

        private bool ToExcel(string strExportSQL)
        {
            
            Excel.Application xlApplication = new Excel.Application();
            Excel.Workbooks xlBooks = (Excel.Workbooks)xlApplication.Workbooks;
            xlBooks = (Excel.Workbooks)xlApplication.Workbooks;
            Excel._Workbook ThisWorkbook = (Excel._Workbook)(xlBooks.Add(Missing.Value));
            Excel.Worksheet xlSheet = (Excel.Worksheet)ThisWorkbook.Sheets[1];
            string strSQL = string.Empty;
            strSQL = strExportSQL;
            try
            {
                int intRow = 1;
                int intCol = 1;
                this.Cursor = Cursors.WaitCursor;
                this.btnExcel.Enabled = false;                

                Excel.Range cell = null;
               
                DataTable dtExport = dbcon.GetDataTable(strSQL);
                #region Export the Title
                foreach (DataColumn drCol in dtExport.Columns)
                {
                    cell = (Excel.Range)xlSheet.Cells[intRow, intCol];
                    cell.Value2 = drCol.Caption.ToString();
                    intCol++;
                }
                #endregion

                #region Export the Row
                foreach (DataRow drExport in dtExport.Rows)
                {
                    intRow++;
                    intCol = 1;
                    foreach (DataColumn drCol in dtExport.Columns)
                    {
                        cell = (Excel.Range)xlSheet.Cells[intRow, intCol];
                        cell.Value2 = CvtFunc.ToString(drExport[drCol]);
                        intCol++;
                    }
                }
                #endregion

            }
            catch
            {

            }
            finally
            {
                xlApplication.Visible = true;
                this.Cursor = Cursors.Default;
                this.btnExcel.Enabled = true;
            }
            return true;
        }

        private bool VaildCheck(string strSQL )
        {
           
            try
            {
                DataRow drCheck = dbcon.GetDataFirstRow(strSQL);
                if (drCheck == null)
                {
                    MessageBox.Show("没有数据导出！", "", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("语句有错，请检查！", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
               
                return false;
            }
           
            return true;            
        }

        private void btnExportSQL_Click(object sender, EventArgs e)
        {
            TitleType FromType= new TitleType();
            
            if (SysVar.CurrentMenuInfo.strMenuId.Substring(0, 3) == "100")
                FromType = TitleType.MeniereDisease;
            else if (SysVar.CurrentMenuInfo.strMenuId.Substring(0, 3) == "200")
                FromType = TitleType.SuddenDeafness;
            else if (SysVar.CurrentMenuInfo.strMenuId.Substring(0, 3) == "300")
                FromType = TitleType.Dizziness;
            else if (SysVar.CurrentMenuInfo.strMenuId.Substring(0, 3) == "400")            
                FromType = TitleType.NOSE;              
            else
                FromType = TitleType.OTHERS;
            
            frmMExportToExcelSel frmSel =new frmMExportToExcelSel ();

            string strSQL = frmSel.strGetExportSQL(FromType, dbcon);
            if (strSQL != "")
            {
                txtSQL.Text = strSQL;            
            }
        }

      
    }
}
